﻿CREATE PROCEDURE dbo.s_Pages_Get
	@LoanID int,
	@ImageTypeID int,
	@PageTypeID varchar(50), -- PageTypeID if Numeric, ContentType if not
	@Start int = 0,
	@Count int = -1
AS


DECLARE @End int

IF @Start <= 0 SET @Start = 1
IF @Count = -1 SET @Count = 2147483646
IF @Count = -1 SET @End = dbo.PageCount(@LoanID,@ImageTypeID)
ELSE SET @End = @Start + @Count - 1
IF ISNUMERIC(@PageTypeID) = 0 SET @PageTypeID = dbo.PageTypeIDByContentType(@PageTypeID)

SELECT @LoanID AS LoanID,ISNULL(t_PageStore.Data, t_PageStore0.Data) AS Data, t_Page.PageNumber, CASE WHEN t_PageStore.Data IS NULL 
               THEN 'NULL' ELSE 'Data' END AS HasData, CASE WHEN t_PageStore0.Data IS NULL THEN 'NULL' ELSE 'Data' END AS HasData0, 
               ISNULL(t_PageStore.PageTypeID, t_PageStore0.PageTypeID) AS PageTypeID,ImageTypeID
FROM  t_Image INNER JOIN
               t_Page ON t_Image.ID = t_Page.ImageID LEFT OUTER JOIN
                   (SELECT *
                    FROM   t_PageStore
                    WHERE PageTypeID = 0) t_PageStore0 ON t_Page.ID = t_PageStore0.PageID LEFT OUTER JOIN
                   (SELECT *
                    FROM   t_PageStore
                    WHERE PageTypeID = @PageTypeID) t_PageStore ON t_Page.ID = t_PageStore.PageID
WHERE (t_Image.LoanID = @LoanID) AND (t_Image.ImageTypeID = @ImageTypeID) AND (t_Page.PageNumber BETWEEN @Start AND @End)
ORDER BY t_Page.PageNumber


